package Test_2019_3_19.util;

import Test_2019_3_19.pojo.Attendance;
import Test_2019_3_19.pojo.TimeSheets;
import Test_2019_3_19.pojo.User;

import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class JdbcUtil {
    private Connection conn = null;
    private PreparedStatement pstmt = null;
    private ResultSet rs = null;
    private static String url;
    private static String user;
    private static String password;
    private static int initCount = 10;// 初始化连接数
    private static int maxCount = 50;// 最大连接数
    private static int currentCount = 0;// 当前连接数
    // 数据库连接的容器（池）
    private static List<Connection> connectionPool = new ArrayList<Connection>();

    static {
        Properties prop = new Properties();
        try {
            InputStream is = JdbcUtil.class.getClassLoader()
                    .getResourceAsStream("Test_2019_3_12.properties");
            prop.load(is);
            // 注册驱动
            String driverClassName = prop.getProperty("driverClassName");
            Class.forName(driverClassName);
            url = prop.getProperty("url");
            user = prop.getProperty("username");
            password = prop.getProperty("password");
            // 初始化一定数量的数据库连接
            for (int i = 0; i < initCount; i++) {
                connectionPool.add(createConnection());
                currentCount++;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建数据库连接
     */
    private static Connection createConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 获取数据库连接
     */
    public static synchronized Connection getConnection() throws SQLException {
        Connection conn = null;
        // 池中存在空闲连接，则获取空闲连接，并将其从池中移除
        if (connectionPool.size() > 0) {
            conn = connectionPool.get(0);
            connectionPool.remove(0);
        } else {
            if (currentCount < maxCount) {
                // 连接数没有达到最大值，创建新连接供其使用
                conn = createConnection();
                currentCount++;
            } else {
                throw new SQLException("已没有空闲的数据库连接。。。");
            }
        }
        return conn;
    }

    /**
     * 释放资源
     */
    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        // 将数据库李连杰重新放置到池中（切记不可关闭）
        if (conn != null) {
            connectionPool.add(conn);
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获得PreparedStatement
     */
    private PreparedStatement getPrepareStatement(String sql) {
        conn = createConnection();
        pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pstmt;
    }

    /**
     * 查询数据
     *
     * @return
     */
    public List<TimeSheets> findPage(int page, int limit, String no, String name, String state, String entranceStart, String entranceEnd) {
        List<TimeSheets> list = new ArrayList<>();
        String sql = "SELECT *FROM TimeSheets " +
                "WHERE no like '%" + no + "%' AND name like '%" + name + "%' AND state like '%" + state + "%'" +
                "AND date >= '" + entranceStart + "' AND date <= '" + entranceEnd + "' " +
                "LIMIT " + (page - 1) * limit + "," + limit + "";
        try {
            pstmt = getPrepareStatement(sql);
            rs = pstmt.executeQuery();
            TimeSheets bd;
            while (rs.next()) {
                bd = new TimeSheets();
                bd.setId(rs.getInt("id"));
                bd.setNo(rs.getInt("no"));
                bd.setName(rs.getString("name"));
                bd.setTime(rs.getString("time"));
                bd.setState(rs.getString("state"));
                bd.setNote(rs.getString("note"));
                bd.setDate(rs.getString("date"));
                list.add(bd);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 查询数据总数
     *
     * @return
     */
    public int findCountPage(String no, String name, String state, String entranceStart, String entranceEnd) {
        int count = 0;
        String sql = "select count(*) from TimeSheets " +
                "WHERE no like '%" + no + "%' AND name like '%" + name + "%' AND state like '%" + state + "%'" +
                "AND date >= '" + entranceStart + "' AND date <= '" + entranceEnd + "' ";
        try {
            pstmt = getPrepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (Exception e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 查询学生数据
     *
     * @return
     */
    public List<User> find() {
        List<User> list = new ArrayList<>();
        String sql = "SELECT *FROM user ";
        try {
            pstmt = getPrepareStatement(sql);
            rs = pstmt.executeQuery();
            User bd;
            while (rs.next()) {
                bd = new User();
                bd.setNo(rs.getInt("no"));
                bd.setName(rs.getString("name"));
                bd.setTime(rs.getString("time"));
                list.add(bd);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 查询学生总数
     *
     * @return
     */
    public int findCount() {
        int count = 0;
        String sql = "select count(*) from user ";
        try {
            pstmt = getPrepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (Exception e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 添加
     */
    public int addAtt(Attendance stu) {
        int rlt = 0;
        String sql = "insert into Attendance(no,state,note,date) values(?,?,?,?)";
        try {
            pstmt = getPrepareStatement(sql);
            pstmt.setInt(1, stu.getNo());
            pstmt.setString(2, stu.getState());
            pstmt.setString(3, stu.getNote());
            pstmt.setString(4, stu.getDate());
            rlt = pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        return rlt;
    }

    /**
     * 修改
     */
//    public int update(String name, String password) {
//        int rlt = 0;
//        try {
//            String sql = "update user SET password = ? where email = ?";
//            pstmt = getPrepareStatement(sql);
//            Object[] params = {password, name};
//            for (int i = 1; i <= params.length; i++) {
//                pstmt.setObject(i, params[i - 1]);
//            }
//            rlt = pstmt.executeUpdate();
//        } catch (SQLException e) {
//            System.out.println(e.getMessage());
//            e.printStackTrace();
//        }
//        return rlt;
//    }

    /**
     * 删除
     */
    public int delete(int no) {
        int rlt = 0;
        try {
            String sql = "DELETE FROM attendance where id = ?";
            pstmt = getPrepareStatement(sql);
            pstmt.setObject(1, no);
            rlt = pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        return rlt;
    }

    /**
     * 查询某学生考勤情况总数
     *
     * @return
     */
    public int Count(int no,String state) {
        int count = 0;
        String sql = "select count(*) from attendance " +
                "WHERE no like '%" + no + "%' AND state like '%" + state + "%' ";
        try {
            pstmt = getPrepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (Exception e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        return count;
    }

}
